const pool = require("./db.js");

class GroupsDao {
    async addGroup(groupName, userId) {
        const [rows, fields] = await pool.execute(
            "insert into `Groups` (group_name, user_id) values (?, ?)", 
            [groupName, userId]
        );
        return rows;
    }

    async deleteGroup(id) {
        const [rows, fields] = await pool.execute(
            "delete from `Groups` where group_id = ?", 
            [id]
        );
        return rows;
    }

    async selectGroupsByPage(groupID, groupName, start, rows) {
        console.log(groupID, groupName, start, rows);
        
        // 确保 start 和 rows 是整数
        start = parseInt(start) || 0;
        rows = parseInt(rows) || 5;
    
        let sql1 = "select DISTINCT * from `groups`";
        let sql2 = "select count(1) as rowCount from `groups`";
        let conditions = [];
    
        if (groupID) {
            conditions.push(`group_id = ${pool.escape(groupID)}`);
        }
        if (groupName) {
            conditions.push(`group_name like ${pool.escape(`%${groupName}%`)}`);
        }
    
        let wheresql = "";
        if (conditions.length > 0) {
            wheresql = " where " + conditions.join(" and ");
        }
    
        sql1 += wheresql + ` limit ${start}, ${rows}`;
        sql2 += wheresql;
    
        const [rows1, fields1] = await pool.execute(sql1);
        const [rows2, fields2] = await pool.execute(sql2);
        const rowCount = rows2[0].rowCount;
        const pageCount = Math.ceil(rowCount / rows);
    
        return { data: rows1, rowCount, pageCount };
    }

    async selectGroupsById(id) {
        const [rows, fields] = await pool.execute(
            "select DISTINCT * from `Groups` where group_id = ?", 
            [id]
        );
        return rows;
    }

    async selectGroupsByUserID(userId){
        const [rows, fields] = await pool.execute(
            "select DISTINCT * from `Groups` where user_id = ?", 
            [userId]
        );
        return rows;
    }
    async selectGroupsByGroupName(groupName) {
        const [rows, fields] = await pool.execute(
            "select DISTINCT * from `Groups` where group_name like '%" + groupName + "%'"
        );
        return rows;
    }

    async selectGroupsByGroupNameAndUserID(groupName, userId) {
        console.log('groupName:'+groupName, userId);
        const [rows, fields] = await pool.execute(
            "select DISTINCT * from `Groups` where group_name = ? and user_id = ?", 
            [groupName, userId]
        );
        return rows;
    }

    async updateGroup(id, groupName) {
        const [rows, fields] = await pool.execute(
            "update `Groups` set group_name = ? where group_id = ?", 
            [groupName, id]
        );
        return rows;
    }

}

module.exports = new GroupsDao();